-- ========================================
-- 物联网车辆管理系统数据库初始化脚本 (PostgreSQL版本)
-- 版本: 1.0
-- 创建时间: 2024
-- ========================================

-- 设置时区
SET timezone = 'Asia/Shanghai';

-- 创建枚举类型
CREATE TYPE user_type_enum AS ENUM ('normal', 'admin');
CREATE TYPE bike_status_enum AS ENUM ('available', 'rented', 'maintenance', 'offline');
CREATE TYPE battery_status_enum AS ENUM ('normal', 'warning', 'fault');
CREATE TYPE order_status_enum AS ENUM ('ongoing', 'completed', 'cancelled', 'timeout');
CREATE TYPE payment_status_enum AS ENUM ('unpaid', 'paid', 'refund');
CREATE TYPE maintenance_status_enum AS ENUM ('pending', 'processing', 'completed', 'cancelled');
CREATE TYPE connection_status_enum AS ENUM ('online', 'offline', 'error');
CREATE TYPE command_type_enum AS ENUM ('open', 'close', 'restart', 'query', 'custom');
CREATE TYPE command_status_enum AS ENUM ('pending', 'sent', 'success', 'failed', 'timeout');
CREATE TYPE payment_method_enum AS ENUM ('alipay', 'wechat', 'balance');
CREATE TYPE payment_record_status_enum AS ENUM ('pending', 'success', 'failed', 'cancelled', 'refund');
CREATE TYPE config_type_enum AS ENUM ('string', 'number', 'boolean', 'json');

-- ========================================
-- 1. 用户表 (users)
-- ========================================
DROP TABLE IF EXISTS users CASCADE;
CREATE TABLE users (
  user_id SERIAL PRIMARY KEY,
  username VARCHAR(50) UNIQUE NOT NULL,
  password VARCHAR(255) NOT NULL,
  user_type user_type_enum DEFAULT 'normal',
  phone VARCHAR(20),
  email VARCHAR(100),
  balance DECIMAL(10,2) DEFAULT 0.00,
  status SMALLINT DEFAULT 1 CHECK (status IN (0, 1)),
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 创建更新时间触发器
CREATE OR REPLACE FUNCTION update_modified_column()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = CURRENT_TIMESTAMP;
    RETURN NEW;
END;
$$ language 'plpgsql';

CREATE TRIGGER update_users_modtime BEFORE UPDATE ON users 
FOR EACH ROW EXECUTE FUNCTION update_modified_column();

-- ========================================
-- 2. 电动车表 (electric_bikes)
-- ========================================
DROP TABLE IF EXISTS electric_bikes CASCADE;
CREATE TABLE electric_bikes (
  bike_id SERIAL PRIMARY KEY,
  bike_code VARCHAR(50) UNIQUE NOT NULL,
  battery_id INTEGER,
  switch_state BOOLEAN DEFAULT FALSE,
  light_threshold REAL DEFAULT 0,
  latitude REAL DEFAULT 0,
  longitude REAL DEFAULT 0,
  is_using BOOLEAN DEFAULT FALSE,
  status bike_status_enum DEFAULT 'available',
  mileage DECIMAL(10,2) DEFAULT 0.00,
  last_maintenance DATE,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TRIGGER update_electric_bikes_modtime BEFORE UPDATE ON electric_bikes 
FOR EACH ROW EXECUTE FUNCTION update_modified_column();

-- ========================================
-- 3. 电池表 (batteries)
-- ========================================
DROP TABLE IF EXISTS batteries CASCADE;
CREATE TABLE batteries (
  battery_id SERIAL PRIMARY KEY,
  battery_code VARCHAR(50) UNIQUE NOT NULL,
  voltage_v1 REAL DEFAULT 0,
  voltage_v2 REAL DEFAULT 0,
  voltage_d REAL DEFAULT 0,
  temperature_t1 REAL DEFAULT 0,
  temperature_t2 REAL DEFAULT 0,
  electricity VARCHAR(10) DEFAULT '0%',
  bike_id INTEGER,
  status battery_status_enum DEFAULT 'normal',
  charge_cycles INTEGER DEFAULT 0,
  health SMALLINT DEFAULT 100 CHECK (health >= 0 AND health <= 100),
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TRIGGER update_batteries_modtime BEFORE UPDATE ON batteries 
FOR EACH ROW EXECUTE FUNCTION update_modified_column();

-- ========================================
-- 4. 骑行记录表 (ride_records)
-- ========================================
DROP TABLE IF EXISTS ride_records CASCADE;
CREATE TABLE ride_records (
  record_id SERIAL PRIMARY KEY,
  user_id INTEGER NOT NULL,
  bike_id INTEGER NOT NULL,
  order_no VARCHAR(32) UNIQUE NOT NULL,
  start_time TIMESTAMP,
  end_time TIMESTAMP,
  start_latitude REAL DEFAULT 0,
  start_longitude REAL DEFAULT 0,
  end_latitude REAL DEFAULT 0,
  end_longitude REAL DEFAULT 0,
  distance REAL DEFAULT 0,
  duration REAL DEFAULT 0,
  average_speed REAL DEFAULT 0,
  max_speed REAL DEFAULT 0,
  cost DECIMAL(10,2) DEFAULT 0.00,
  status order_status_enum DEFAULT 'ongoing',
  payment_status payment_status_enum DEFAULT 'unpaid',
  payment_method VARCHAR(20),
  remark TEXT,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TRIGGER update_ride_records_modtime BEFORE UPDATE ON ride_records 
FOR EACH ROW EXECUTE FUNCTION update_modified_column();

-- ========================================
-- 5. 维护记录表 (maintenance_records)
-- ========================================
DROP TABLE IF EXISTS maintenance_records CASCADE;
CREATE TABLE maintenance_records (
  maintenance_id SERIAL PRIMARY KEY,
  bike_id INTEGER NOT NULL,
  maintenance_type VARCHAR(50) NOT NULL,
  maintenance_info TEXT,
  inspector_id INTEGER,
  inspector_name VARCHAR(50),
  fault_description TEXT,
  repair_description TEXT,
  cost DECIMAL(10,2) DEFAULT 0.00,
  status maintenance_status_enum DEFAULT 'pending',
  inspection_time TIMESTAMP,
  repair_time TIMESTAMP,
  completion_time TIMESTAMP,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TRIGGER update_maintenance_records_modtime BEFORE UPDATE ON maintenance_records 
FOR EACH ROW EXECUTE FUNCTION update_modified_column();

-- ========================================
-- 6. IoT设备表 (iot_devices)
-- ========================================
DROP TABLE IF EXISTS iot_devices CASCADE;
CREATE TABLE iot_devices (
  device_id SERIAL PRIMARY KEY,
  device_code VARCHAR(50) UNIQUE NOT NULL,
  device_type VARCHAR(100) NOT NULL,
  device_name VARCHAR(100) NOT NULL,
  bike_id INTEGER,
  switch_status BOOLEAN DEFAULT FALSE,
  connection_status connection_status_enum DEFAULT 'offline',
  last_online_time TIMESTAMP,
  firmware_version VARCHAR(20),
  hardware_version VARCHAR(20),
  mqtt_topic VARCHAR(200),
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TRIGGER update_iot_devices_modtime BEFORE UPDATE ON iot_devices 
FOR EACH ROW EXECUTE FUNCTION update_modified_column();

-- ========================================
-- 7. 设备指令表 (device_commands)
-- ========================================
DROP TABLE IF EXISTS device_commands CASCADE;
CREATE TABLE device_commands (
  command_id SERIAL PRIMARY KEY,
  device_id INTEGER NOT NULL,
  command_type command_type_enum NOT NULL,
  command_name VARCHAR(100) NOT NULL,
  send_order VARCHAR(255) NOT NULL,
  expected_response VARCHAR(255),
  actual_response VARCHAR(255),
  status command_status_enum DEFAULT 'pending',
  retry_count SMALLINT DEFAULT 0,
  send_time TIMESTAMP,
  response_time TIMESTAMP,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TRIGGER update_device_commands_modtime BEFORE UPDATE ON device_commands 
FOR EACH ROW EXECUTE FUNCTION update_modified_column();

-- ========================================
-- 8. 传感器数据表 (sensor_data)
-- ========================================
DROP TABLE IF EXISTS sensor_data CASCADE;
CREATE TABLE sensor_data (
  id BIGSERIAL PRIMARY KEY,
  device_id INTEGER NOT NULL,
  bike_id INTEGER,
  data_type VARCHAR(50) NOT NULL,
  data_value DECIMAL(10,4),
  data_unit VARCHAR(20),
  latitude REAL,
  longitude REAL,
  collected_at TIMESTAMP NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- ========================================
-- 9. 支付记录表 (payment_records)
-- ========================================
DROP TABLE IF EXISTS payment_records CASCADE;
CREATE TABLE payment_records (
  payment_id SERIAL PRIMARY KEY,
  record_id INTEGER NOT NULL,
  user_id INTEGER NOT NULL,
  order_no VARCHAR(32) UNIQUE NOT NULL,
  payment_no VARCHAR(64),
  amount DECIMAL(10,2) NOT NULL,
  payment_method payment_method_enum NOT NULL,
  status payment_record_status_enum DEFAULT 'pending',
  trade_status VARCHAR(50),
  callback_info TEXT,
  refund_amount DECIMAL(10,2) DEFAULT 0.00,
  refund_reason VARCHAR(200),
  paid_at TIMESTAMP,
  refund_at TIMESTAMP,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TRIGGER update_payment_records_modtime BEFORE UPDATE ON payment_records 
FOR EACH ROW EXECUTE FUNCTION update_modified_column();

-- ========================================
-- 10. 系统配置表 (system_configs)
-- ========================================
DROP TABLE IF EXISTS system_configs CASCADE;
CREATE TABLE system_configs (
  id SERIAL PRIMARY KEY,
  config_key VARCHAR(100) UNIQUE NOT NULL,
  config_value TEXT,
  config_desc VARCHAR(200),
  config_type config_type_enum DEFAULT 'string',
  is_enabled BOOLEAN DEFAULT TRUE,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TRIGGER update_system_configs_modtime BEFORE UPDATE ON system_configs 
FOR EACH ROW EXECUTE FUNCTION update_modified_column();

-- ========================================
-- 创建外键约束
-- ========================================
ALTER TABLE electric_bikes ADD CONSTRAINT fk_electric_bikes_battery 
  FOREIGN KEY (battery_id) REFERENCES batteries(battery_id);

ALTER TABLE batteries ADD CONSTRAINT fk_batteries_bike 
  FOREIGN KEY (bike_id) REFERENCES electric_bikes(bike_id);

ALTER TABLE ride_records ADD CONSTRAINT fk_ride_records_user 
  FOREIGN KEY (user_id) REFERENCES users(user_id);

ALTER TABLE ride_records ADD CONSTRAINT fk_ride_records_bike 
  FOREIGN KEY (bike_id) REFERENCES electric_bikes(bike_id);

ALTER TABLE maintenance_records ADD CONSTRAINT fk_maintenance_records_bike 
  FOREIGN KEY (bike_id) REFERENCES electric_bikes(bike_id);

ALTER TABLE iot_devices ADD CONSTRAINT fk_iot_devices_bike 
  FOREIGN KEY (bike_id) REFERENCES electric_bikes(bike_id);

ALTER TABLE device_commands ADD CONSTRAINT fk_device_commands_device 
  FOREIGN KEY (device_id) REFERENCES iot_devices(device_id);

ALTER TABLE sensor_data ADD CONSTRAINT fk_sensor_data_device 
  FOREIGN KEY (device_id) REFERENCES iot_devices(device_id);

ALTER TABLE sensor_data ADD CONSTRAINT fk_sensor_data_bike 
  FOREIGN KEY (bike_id) REFERENCES electric_bikes(bike_id);

ALTER TABLE payment_records ADD CONSTRAINT fk_payment_records_record 
  FOREIGN KEY (record_id) REFERENCES ride_records(record_id);

ALTER TABLE payment_records ADD CONSTRAINT fk_payment_records_user 
  FOREIGN KEY (user_id) REFERENCES users(user_id);

-- ========================================
-- 创建索引
-- ========================================

-- 用户表索引
CREATE INDEX idx_users_phone ON users(phone);
CREATE INDEX idx_users_created_at ON users(created_at);

-- 电动车表索引
CREATE INDEX idx_electric_bikes_battery_id ON electric_bikes(battery_id);
CREATE INDEX idx_electric_bikes_status ON electric_bikes(status);
CREATE INDEX idx_electric_bikes_location ON electric_bikes(latitude, longitude);
CREATE INDEX idx_electric_bikes_updated_at ON electric_bikes(updated_at);
CREATE INDEX idx_electric_bikes_status_location ON electric_bikes(status, latitude, longitude);

-- 电池表索引
CREATE INDEX idx_batteries_bike_id ON batteries(bike_id);
CREATE INDEX idx_batteries_status ON batteries(status);
CREATE INDEX idx_batteries_updated_at ON batteries(updated_at);

-- 骑行记录表索引
CREATE INDEX idx_ride_records_user_id ON ride_records(user_id);
CREATE INDEX idx_ride_records_bike_id ON ride_records(bike_id);
CREATE INDEX idx_ride_records_start_time ON ride_records(start_time);
CREATE INDEX idx_ride_records_status ON ride_records(status);
CREATE INDEX idx_ride_records_created_at ON ride_records(created_at);
CREATE INDEX idx_ride_records_user_status_time ON ride_records(user_id, status, created_at);

-- 维护记录表索引
CREATE INDEX idx_maintenance_records_bike_id ON maintenance_records(bike_id);
CREATE INDEX idx_maintenance_records_inspector_id ON maintenance_records(inspector_id);
CREATE INDEX idx_maintenance_records_status ON maintenance_records(status);
CREATE INDEX idx_maintenance_records_inspection_time ON maintenance_records(inspection_time);

-- IoT设备表索引
CREATE INDEX idx_iot_devices_device_type ON iot_devices(device_type);
CREATE INDEX idx_iot_devices_bike_id ON iot_devices(bike_id);
CREATE INDEX idx_iot_devices_connection_status ON iot_devices(connection_status);
CREATE INDEX idx_iot_devices_last_online_time ON iot_devices(last_online_time);

-- 设备指令表索引
CREATE INDEX idx_device_commands_device_id ON device_commands(device_id);
CREATE INDEX idx_device_commands_command_type ON device_commands(command_type);
CREATE INDEX idx_device_commands_status ON device_commands(status);
CREATE INDEX idx_device_commands_send_time ON device_commands(send_time);

-- 传感器数据表索引
CREATE INDEX idx_sensor_data_device_id ON sensor_data(device_id);
CREATE INDEX idx_sensor_data_bike_id ON sensor_data(bike_id);
CREATE INDEX idx_sensor_data_data_type ON sensor_data(data_type);
CREATE INDEX idx_sensor_data_collected_at ON sensor_data(collected_at);
CREATE INDEX idx_sensor_data_created_at ON sensor_data(created_at);
CREATE INDEX idx_sensor_data_device_type_time ON sensor_data(device_id, data_type, collected_at);

-- 支付记录表索引
CREATE INDEX idx_payment_records_record_id ON payment_records(record_id);
CREATE INDEX idx_payment_records_user_id ON payment_records(user_id);
CREATE INDEX idx_payment_records_payment_no ON payment_records(payment_no);
CREATE INDEX idx_payment_records_status ON payment_records(status);
CREATE INDEX idx_payment_records_paid_at ON payment_records(paid_at);
CREATE INDEX idx_payment_records_user_status_time ON payment_records(user_id, status, created_at);

-- ========================================
-- 初始化数据
-- ========================================

-- 插入默认管理员用户 (密码: admin123)
INSERT INTO users (username, password, user_type, phone, email, balance) VALUES
('admin', '$2a$10$7JB720yubVSeLVjq6sHnK.Ke3UqfF.X1tJfhLjTQQ2aVzZrI.W5K6', 'admin', '13800138000', 'admin@example.com', 1000.00),
('testuser', '$2a$10$7JB720yubVSeLVjq6sHnK.Ke3UqfF.X1tJfhLjTQQ2aVzZrI.W5K6', 'normal', '13800138001', 'test@example.com', 100.00);

-- 插入测试电池数据
INSERT INTO batteries (battery_code, voltage_v1, voltage_v2, voltage_d, temperature_t1, temperature_t2, electricity, status, health) VALUES
('BAT001', 12.5, 12.3, 0.2, 25.5, 26.0, '85%', 'normal', 90),
('BAT002', 12.8, 12.6, 0.2, 24.8, 25.2, '92%', 'normal', 95),
('BAT003', 11.9, 11.7, 0.2, 26.1, 26.5, '78%', 'warning', 80),
('BAT004', 12.7, 12.5, 0.2, 25.0, 25.5, '88%', 'normal', 92),
('BAT005', 12.2, 12.0, 0.2, 26.5, 27.0, '82%', 'normal', 85);

-- 插入测试电动车数据
INSERT INTO electric_bikes (bike_code, battery_id, switch_state, light_threshold, latitude, longitude, status, mileage) VALUES
('BIKE001', 1, FALSE, 300, 39.9042, 116.4074, 'available', 156.8),
('BIKE002', 2, FALSE, 280, 39.9052, 116.4084, 'available', 234.5),
('BIKE003', 3, TRUE, 320, 39.9032, 116.4064, 'rented', 89.2),
('BIKE004', 4, FALSE, 290, 39.9062, 116.4094, 'available', 342.1),
('BIKE005', 5, FALSE, 310, 39.9022, 116.4054, 'maintenance', 567.8);

-- 插入测试IoT设备数据
INSERT INTO iot_devices (device_code, device_type, device_name, bike_id, connection_status, firmware_version, mqtt_topic) VALUES
('DEV001', 'GPS_TRACKER', 'GPS追踪器1', 1, 'online', 'v1.2.3', 'device/DEV001/status'),
('DEV002', 'SMART_LOCK', '智能锁1', 1, 'online', 'v2.1.0', 'device/DEV002/status'),
('DEV003', 'GPS_TRACKER', 'GPS追踪器2', 2, 'online', 'v1.2.3', 'device/DEV003/status'),
('DEV004', 'BATTERY_MONITOR', '电池监控器1', 1, 'online', 'v1.0.5', 'device/DEV004/status'),
('DEV005', 'TEMPERATURE_SENSOR', '温度传感器1', 1, 'online', 'v1.1.2', 'device/DEV005/status');

-- 插入测试设备指令数据
INSERT INTO device_commands (device_id, command_type, command_name, send_order, expected_response) VALUES
(2, 'open', '开锁指令', 'UNLOCK_BIKE', 'UNLOCK_SUCCESS'),
(2, 'close', '锁车指令', 'LOCK_BIKE', 'LOCK_SUCCESS'),
(1, 'query', '位置查询', 'GET_LOCATION', 'LOCATION_DATA'),
(4, 'query', '电池状态查询', 'GET_BATTERY_STATUS', 'BATTERY_DATA'),
(5, 'query', '温度查询', 'GET_TEMPERATURE', 'TEMP_DATA');

-- 插入系统配置数据
INSERT INTO system_configs (config_key, config_value, config_desc, config_type) VALUES
('bike.rental.price_per_hour', '2.00', '每小时租车费用', 'number'),
('bike.rental.price_per_minute', '0.10', '每分钟租车费用', 'number'),
('bike.rental.max_duration', '24', '最大租车时长(小时)', 'number'),
('battery.warning_threshold', '20', '电池预警阈值(%)', 'number'),
('battery.critical_threshold', '10', '电池严重告警阈值(%)', 'number'),
('maintenance.check_interval', '30', '维护检查间隔(天)', 'number'),
('payment.timeout', '300', '支付超时时间(秒)', 'number'),
('mqtt.broker_url', 'ws://localhost:8083/mqtt', 'MQTT代理地址', 'string'),
('mqtt.username', 'emqx', 'MQTT用户名', 'string'),
('mqtt.password', 'public', 'MQTT密码', 'string'),
('alipay.app_id', '9021000125695087', '支付宝应用ID', 'string'),
('system.maintenance_mode', 'false', '系统维护模式', 'boolean'),
('bike.max_distance_per_day', '50', '每日最大骑行距离(KM)', 'number');

-- 插入一些测试传感器数据
INSERT INTO sensor_data (device_id, bike_id, data_type, data_value, data_unit, latitude, longitude, collected_at) VALUES
(1, 1, 'gps', NULL, NULL, 39.9042, 116.4074, CURRENT_TIMESTAMP),
(4, 1, 'battery', 85.50, '%', NULL, NULL, CURRENT_TIMESTAMP),
(5, 1, 'temperature', 25.5, '°C', NULL, NULL, CURRENT_TIMESTAMP),
(1, 2, 'gps', NULL, NULL, 39.9052, 116.4084, CURRENT_TIMESTAMP),
(4, 2, 'battery', 92.00, '%', NULL, NULL, CURRENT_TIMESTAMP);

-- ========================================
-- 创建视图
-- ========================================

-- 创建视图：车辆状态概览
CREATE OR REPLACE VIEW v_bike_overview AS
SELECT 
    eb.bike_id,
    eb.bike_code,
    eb.status,
    eb.latitude,
    eb.longitude,
    eb.mileage,
    eb.is_using,
    b.electricity,
    b.temperature_t1,
    b.status as battery_status,
    b.health as battery_health,
    COUNT(DISTINCT id.device_id) as device_count,
    SUM(CASE WHEN id.connection_status = 'online' THEN 1 ELSE 0 END) as online_devices,
    CASE 
        WHEN eb.updated_at > CURRENT_TIMESTAMP - INTERVAL '10 minutes' THEN 'online'
        ELSE 'offline'
    END as online_status,
    eb.updated_at
FROM electric_bikes eb
LEFT JOIN batteries b ON eb.battery_id = b.battery_id
LEFT JOIN iot_devices id ON eb.bike_id = id.bike_id
GROUP BY eb.bike_id, eb.bike_code, eb.status, eb.latitude, eb.longitude, 
         eb.mileage, eb.is_using, b.electricity, b.temperature_t1, 
         b.status, b.health, eb.updated_at;

-- 创建视图：用户骑行统计
CREATE OR REPLACE VIEW v_user_ride_stats AS
SELECT 
    u.user_id,
    u.username,
    COUNT(rr.record_id) as total_rides,
    COALESCE(SUM(rr.distance), 0) as total_distance,
    COALESCE(SUM(rr.duration), 0) as total_duration,
    COALESCE(SUM(rr.cost), 0) as total_cost,
    COALESCE(AVG(rr.average_speed), 0) as avg_speed,
    MAX(rr.created_at) as last_ride_time
FROM users u
LEFT JOIN ride_records rr ON u.user_id = rr.user_id AND rr.status = 'completed'
GROUP BY u.user_id, u.username;

-- ========================================
-- 创建存储过程/函数
-- ========================================

-- 创建函数：计算骑行费用
CREATE OR REPLACE FUNCTION calculate_rental_cost(p_record_id INTEGER)
RETURNS DECIMAL(10,2) AS $$
DECLARE
    v_duration REAL DEFAULT 0;
    v_price_per_hour DECIMAL(10,2) DEFAULT 2.00;
    v_price_per_minute DECIMAL(10,2) DEFAULT 0.10;
    v_cost DECIMAL(10,2);
BEGIN
    -- 获取骑行时长
    SELECT duration INTO v_duration 
    FROM ride_records 
    WHERE record_id = p_record_id;
    
    -- 获取价格配置
    SELECT CAST(config_value AS DECIMAL(10,2)) INTO v_price_per_hour
    FROM system_configs 
    WHERE config_key = 'bike.rental.price_per_hour' AND is_enabled = TRUE;
    
    SELECT CAST(config_value AS DECIMAL(10,2)) INTO v_price_per_minute
    FROM system_configs 
    WHERE config_key = 'bike.rental.price_per_minute' AND is_enabled = TRUE;
    
    -- 计算费用（按分钟计费，最少15分钟起步）
    v_cost := GREATEST(15, ROUND(v_duration * 60)) * v_price_per_minute;
    
    -- 更新订单费用
    UPDATE ride_records 
    SET cost = v_cost 
    WHERE record_id = p_record_id;
    
    RETURN v_cost;
END;
$$ LANGUAGE plpgsql;

-- 创建函数：更新车辆状态
CREATE OR REPLACE FUNCTION update_bike_status(
    p_bike_id INTEGER,
    p_latitude REAL,
    p_longitude REAL,
    p_battery_level VARCHAR(10)
) RETURNS VOID AS $$
BEGIN
    -- 更新车辆位置
    UPDATE electric_bikes 
    SET latitude = p_latitude, 
        longitude = p_longitude,
        updated_at = CURRENT_TIMESTAMP
    WHERE bike_id = p_bike_id;
    
    -- 更新电池信息
    UPDATE batteries 
    SET electricity = p_battery_level,
        updated_at = CURRENT_TIMESTAMP
    WHERE bike_id = p_bike_id;
    
    -- 插入传感器数据记录
    INSERT INTO sensor_data (device_id, bike_id, data_type, latitude, longitude, collected_at)
    SELECT device_id, p_bike_id, 'gps', p_latitude, p_longitude, CURRENT_TIMESTAMP
    FROM iot_devices 
    WHERE bike_id = p_bike_id AND device_type = 'GPS_TRACKER'
    LIMIT 1;
END;
$$ LANGUAGE plpgsql;

-- 创建函数：生成订单号
CREATE OR REPLACE FUNCTION generate_order_no()
RETURNS VARCHAR(32) AS $$
DECLARE
    order_no VARCHAR(32);
    current_date VARCHAR(8);
    sequence_no INTEGER;
BEGIN
    -- 获取当前日期
    current_date := TO_CHAR(CURRENT_DATE, 'YYYYMMDD');
    
    -- 获取当日订单序号
    SELECT COALESCE(MAX(CAST(SUBSTRING(order_no, 9) AS INTEGER)), 0) + 1 
    INTO sequence_no
    FROM ride_records 
    WHERE order_no LIKE current_date || '%';
    
    -- 生成订单号：日期+6位序号
    order_no := current_date || LPAD(sequence_no::TEXT, 6, '0');
    
    RETURN order_no;
END;
$$ LANGUAGE plpgsql;

-- ========================================
-- 创建触发器
-- ========================================

-- 创建触发器：骑行记录插入时自动生成订单号
CREATE OR REPLACE FUNCTION trigger_generate_order_no()
RETURNS TRIGGER AS $$
BEGIN
    IF NEW.order_no IS NULL OR NEW.order_no = '' THEN
        NEW.order_no := generate_order_no();
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER tr_ride_record_insert 
BEFORE INSERT ON ride_records
FOR EACH ROW EXECUTE FUNCTION trigger_generate_order_no();

-- 创建触发器：电池状态更新时检查预警
CREATE OR REPLACE FUNCTION trigger_battery_status_check()
RETURNS TRIGGER AS $$
DECLARE
    warning_threshold INTEGER DEFAULT 20;
    critical_threshold INTEGER DEFAULT 10;
    current_level INTEGER;
BEGIN
    -- 获取配置的预警阈值
    SELECT CAST(config_value AS INTEGER) INTO warning_threshold
    FROM system_configs 
    WHERE config_key = 'battery.warning_threshold' AND is_enabled = TRUE;
    
    SELECT CAST(config_value AS INTEGER) INTO critical_threshold
    FROM system_configs 
    WHERE config_key = 'battery.critical_threshold' AND is_enabled = TRUE;
    
    -- 获取当前电量（去掉%符号）
    current_level := CAST(REPLACE(NEW.electricity, '%', '') AS INTEGER);
    
    -- 更新电池状态
    IF current_level <= critical_threshold THEN
        NEW.status := 'fault';
    ELSIF current_level <= warning_threshold THEN
        NEW.status := 'warning';
    ELSE
        NEW.status := 'normal';
    END IF;
    
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER tr_battery_update 
BEFORE UPDATE ON batteries
FOR EACH ROW EXECUTE FUNCTION trigger_battery_status_check();

-- ========================================
-- 数据库初始化完成
-- ========================================
SELECT '========================================';
SELECT '数据库初始化完成！PostgreSQL版本';
SELECT '========================================';
SELECT CONCAT('数据库名称: ', current_database());
SELECT CONCAT('表数量: ', COUNT(*)) FROM information_schema.tables WHERE table_schema = 'public' AND table_type = 'BASE TABLE';
SELECT CONCAT('初始化时间: ', CURRENT_TIMESTAMP);
SELECT '========================================';